

from flask import Blueprint
from flask import request
from flask import jsonify
import psycopg2.extras
from database import db_pool_singleton
from database.spu_table_methods import SpuTableMethods


spu_blueprint = Blueprint('spu', __name__)



@spu_blueprint.route('/insert_spu', methods=['POST'])
def insert_spu():
    product_name = request.form.get('product_name')
    product_description = request.form.get('product_description')


    if not product_name or not product_description:
        args_error = {
            "status": -1,
            "errorCode": 1001,
            "errorMsg": "product_name and product_description are required",
            "resultBody": {}
        }
        return jsonify(args_error)

    with db_pool_singleton.getconn() as conn:
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # cursor for executing queries
        spu_table_method = SpuTableMethods(conn, cur)
        spu_table_method.insert_spu(product_name, product_description)

        insert_success = {
            "status": 1,
            "errorCode": None,  
            "errorMsg": None,   
            "resultBody": {"product_name": product_name, "product_description": product_description}

        }
        return jsonify(insert_success)

    
@spu_blueprint.route('/get_spu_by_id', methods=['GET'])
def get_spu_by_id():
    spu_id = request.args.get('spu_id')

    if not spu_id:
        args_error = {
            "status": -1,
            "errorCode": 1002,
            "errorMsg": "spu_id is required",
            "resultBody": {}
        }
        return jsonify(args_error)
    

    with db_pool_singleton.getconn() as conn:
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # cursor for executing queries
        spu_table_method = SpuTableMethods(conn, cur)
        spu = spu_table_method.get_spu_by_id(spu_id)

        if not spu:
            spu_not_found = {
                "status": -1,
                "errorCode": 1003,
                "errorMsg": "spu not found",
                "resultBody": {}
            }
            return jsonify(spu_not_found)
        else:
            spu_found = {
                "status": 1,
                "errorCode": None,
                "errorMsg": None,
                "resultBody": spu
            }
            return jsonify(spu_found)
        

@spu_blueprint.route('/get_spu_by_name', methods=['GET'])
def get_spu_by_name():
    product_name = request.args.get('product_name')

    if not product_name:
        args_error = {
            "status": -1,
            "errorCode": 1004,
            "errorMsg": "product_name is required",
            "resultBody": None
        }
        return jsonify(args_error)
    

    with db_pool_singleton.getconn() as conn:
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # cursor for executing queries
        spu_table_method = SpuTableMethods(conn, cur)
        spu = spu_table_method.get_spu_by_name(product_name)

        if not spu:
            spu_not_found = {
                "status": -1,
                "errorCode": 1005,
                "errorMsg": "spu not found",
                "resultBody": None
            }
            return jsonify(spu_not_found)
        else:
            spu_found = {
                "status": 1,
                "errorCode": None,
                "errorMsg": None,
                "resultBody": spu
            }
            return jsonify(spu_found)
        


def get_all_spus(self, limit=10, page=1):

        start = (page - 1) * limit

        sql = "SELECT * FROM spu LIMIT %s OFFSET %s"

        self.cursor.execute(sql, (limit, start))

        result = self.cursor.fetchall()

        return result

@spu_blueprint.route('/get_all_spus', methods=['GET'])
def get_all_spus():

    limit = request.args.get('limit')
    page = request.args.get('page')

    if not limit or not page:
        args_error = {
            "status": -1,
            "errorCode": 1006,
            "errorMsg": "limit and page are required",
            "resultBody": None
        }
        return jsonify(args_error)
    
    with db_pool_singleton.getconn() as conn:
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # cursor for executing queries
        spu_table_method = SpuTableMethods(conn, cur)
        spus = spu_table_method.get_all_spus(limit, page)

        if not spus:
            spus_not_found = {
                "status": -1,
                "errorCode": 1007,
                "errorMsg": "spus not found",
                "resultBody": None
            }
            return jsonify(spus_not_found)
        else:
            spus_found = {
                "status": 1,
                "errorCode": None,
                "errorMsg": None,
                "resultBody": spus
            }
            return jsonify(spus_found)

@spu_blueprint.route('/update_spu', methods=['POST'])
def update_spu():
    spu_id = request.form.get('spu_id')
    product_name = request.form.get('product_name')
    product_description = request.form.get('product_description')

    if not spu_id or not product_name or not product_description:
        args_error = {
            "status": -1,
            "errorCode": 1008,
            "errorMsg": "spu_id, product_name and product_description are required",
            "resultBody": None
        }
        return jsonify(args_error)

    with db_pool_singleton.getconn() as conn:
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # cursor for executing queries
        spu_table_method = SpuTableMethods(conn, cur)
        spu_table_method.update_spu(spu_id, product_name, product_description)

        update_success = {
            "status": 1,
            "errorCode": None,  
            "errorMsg": None,   
            "resultBody": {
                "spu_id": spu_id, 
                "product_name": product_name, 
                "product_description": product_description
            }
        }
        return jsonify(update_success)
    

@spu_blueprint.route('/delete_spu', methods=['POST'])
def delete_spu():
    spu_id = request.form.get('spu_id')

    if not spu_id:
        args_error = {
            "status": -1,
            "errorCode": 1009,
            "errorMsg": "spu_id is required",
            "resultBody": None
        }
        return jsonify(args_error)

    with db_pool_singleton.getconn() as conn:
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # cursor for executing queries
        spu_table_method = SpuTableMethods(conn, cur)
        spu_table_method.delete_spu(spu_id)

        delete_success = {
            "status": 1,
            "errorCode": None,  
            "errorMsg": None,   
            "resultBody": {"spu_id": spu_id}
        }        
        return jsonify(delete_success)
    

@spu_blueprint.route('/select_spu_by_keyword', methods=['GET'])
def select_spu_by_keyword():
    keyword = request.args.get('keyword')

    if not keyword:
        args_error = {
            "status": -1,
            "errorCode": 1010,
            "errorMsg": "keyword is required",
            "resultBody": None
        }
        return jsonify(args_error)

    with db_pool_singleton.getconn() as conn:
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # cursor for executing queries
        spu_table_method = SpuTableMethods(conn, cur)
        spus = spu_table_method.select_spu_by_keyword(keyword)
        spus_found = {
            "status": 1,
            "errorCode": None,
            "errorMsg": None,
            "resultBody": spus
        }
        return jsonify(spus_found)

@spu_blueprint.route('/select_spu_by_keyword_where_description', methods=['GET'])
def select_spu_by_keyword_where_description():
    keyword = request.args.get('keyword')


    if not keyword: 
        args_error = {  
            "status": -1,  
            "errorCode": 1011,  
            "errorMsg": "keyword is required",  
            "resultBody": None  
        }  
        return jsonify(args_error)  


    with db_pool_singleton.getconn() as conn:  
        cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # cursor for executing queries  
        spu_table_method = SpuTableMethods(conn, cur)  
        spus = spu_table_method.select_spu_by_keyword_where_description(keyword)  
        spus_found = {  
            "status": 1,  
            "errorCode": None,  
            "errorMsg": None,  
            "resultBody": spus  
        }  
        return jsonify(spus_found)  

